package com.scorekeeper.utils;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import com.scorekeeper.Main;
import com.scorekeeper.objects.Division;
import com.scorekeeper.objects.Extension;
import com.scorekeeper.objects.Game;
import com.scorekeeper.objects.Season;
import com.scorekeeper.objects.Extension.CastType;
import com.scorekeeper.objects.Team;

import android.app.Application;
import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.Context;
import android.content.ContextWrapper;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

/***
 * Database wrapper class to handle transactions
 * @author Darrel Siegle
 *
 */
@SuppressWarnings("unused")
public class DBProvider {
	OpenHelper DBHelper = null;

	private static final String DATABASE_NAME = "keeper.db";
	private static final int DATABASE_VERSION = 1;
	private static final String TAG = "DBProvider";
	
	private static SQLiteDatabase DBInstance;
	private static Logger logger = new Logger();
	
	public static enum Table {
		SEASON(0, "SEASON", new Season(), 
			"SELECT * FROM SEASON", 
			"SELECT * FROM SEASON WHERE Id = ?", null),
		TEAM(1, "TEAM", new Team(), 
			"SELECT * FROM TEAM", "SELECT * FROM TEAM WHERE Id = ?",
			"SELECT * FROM TEAM WHERE DivisionId = ?"),
		GAME(2, "GAME", new Game(), 
			"SELECT * FROM GAME", "SELECT * FROM GAME WHERE Id = ?", 
			"SELECT * FROM GAME WHERE HomeTeamId = ? OR AwayTeamId = ?"),
		DIVISION(3, "DIVISION", new Division(), 
			"SELECT * FROM DIVISION", "SELECT * FROM DIVISION WHERE Id = ?", null),
		EXTENSION(4, "EXTENSION", new Extension(), 
			"SELECT ev.Id, ev.Name, ev.Value, ev.TeamId, et.Type FROM ext_value ev JOIN ext_type et ON et.Type = ev.ExtType", 
			"SELECT ev.Id, ev.Name, ev.Value, ev.TeamId, et.Type FROM ext_value ev JOIN ext_type et ON et.Type = ev.ExtType WHERE ev.Id = ?",
			"SELECT ev.Id, ev.Name, ev.Value, ev.TeamId, et.Type FROM ext_value ev JOIN ext_type et ON et.Type = ev.ExtType WHERE ev.TeamId = ?");
		
		private final int tblIndex;
		private final String tblName;
		private final String tblSelectAll;
		private final String tblSelectByPrimaryIdx;
		private final String tblSelectBySecondaryIdx;
		private final Object tblObject;

		private Table(int index, String tableName, Object object, String selectAll, String selectByPrimaryIdx, String selectBySecondaryIdx) { 
			tblIndex = index; 
			tblName = tableName;
			tblSelectAll = selectAll;
			tblSelectByPrimaryIdx = selectByPrimaryIdx;
			tblSelectBySecondaryIdx = selectBySecondaryIdx;
			tblObject = object;
		}

		final int Index() { 
			return tblIndex; 
		}

		public final String Name() {
			return tblName;
		}

		final String SelectQuery(boolean selectAll, boolean primary) {
			return (selectAll == true) ?
					tblSelectAll : 
						(primary == true) ? 
								tblSelectByPrimaryIdx : tblSelectBySecondaryIdx;
		}

		final Object objContainer() {
			return tblObject;
		}
	}
	
	public DBProvider() {
		try {
			//TODO: something about the context crap!
			DBHelper = new OpenHelper(Main.ctx);
			this.open();
			
		} catch (SQLiteException sqlE) {
			Logger.LogError(TAG, sqlE);
		}
	}
	
	//---opens the database---
	public DBProvider open() throws SQLException 
	{
		try {
			DBInstance = DBHelper.getWritableDatabase();
			DBInstance.setLockingEnabled(false);
			
			return this;
		}
		catch (SQLiteException sqlE) {
			Logger.LogError(TAG, sqlE);
			return null;
		}
		catch (Exception ex) {
			Logger.LogError(TAG, ex);
			return null;
		}
	}
	
	//---closes the database---
	public void close() 
	{
		SQLiteDatabase.releaseMemory();
		DBInstance.close();
	}
	
	//---checks if the cursor is not null and moves to first record
	private static boolean isValidCursor(Cursor cur) {
		if (cur != null) {
			if (cur.moveToFirst()) {
				return true;
			}
		}

		return false;
	}
	
	public Date lastUpdateDate(Table tbl) {
		Date lastUpdated = null;
		
		this.open();
		Cursor cursor = DBInstance.rawQuery("SELECT UpdatedDate FROM updates WHERE UpdatedTable = ?", new String[] { tbl.name() });
		
		if (isValidCursor(cursor)) {
			lastUpdated = new Date(cursor.getLong(0));
		}
		this.close();
		
		return lastUpdated;
	}
	
	//---helper class to create and update the database
	public class OpenHelper extends SQLiteOpenHelper {
		private static final String TAG = "OpenHelper";
		
		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			//this.context = context;
		}

		//Create all the tables
		@Override
		public void onCreate(SQLiteDatabase DBInstance) {

			DBInstance.execSQL("CREATE TABLE division (" +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " + 
					"DivisionName TEXT, " +
					"DivisionAcro TEXT);");
			
			DBInstance.execSQL("CREATE TABLE team ( " + 
					"Id      INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " + 
					"TeamName    TEXT    NOT NULL ON CONFLICT FAIL, " + 
					"Acro        TEXT    NOT NULL ON CONFLICT FAIL, " + 
					"DivisionId  INTEGER NOT NULL ON CONFLICT FAIL, " + 
					"GamesPlayed INTEGER DEFAULT ( 0 ), " + 
					"Wins        INTEGER DEFAULT ( 0 ), " + 
					"Losses      INTEGER DEFAULT ( 0 ), " + 
					"Points      INTEGER DEFAULT ( 0 ), " + 
					"FOREIGN KEY ( DivisionId ) REFERENCES Division ( DivisionId ));");

			DBInstance.execSQL("CREATE TABLE game ( " +
					"Id     INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " +
					"GameDate   NUMERIC, " +
					"HomeTeamId INTEGER NOT NULL ON CONFLICT FAIL, " +
					"AwayTeamId INTEGER NOT NULL ON CONFLICT FAIL, " +
					"HomeScore  INTEGER DEFAULT ( 0 ), " +
					"AwayScore  INTEGER DEFAULT ( 0 ), " +
					"SeasonId   INTEGER REFERENCES season ( SeasonId ), " +
					"FOREIGN KEY ( HomeTeamId, AwayTeamId ) REFERENCES team ( TeamId, TeamId ));");

			DBInstance.execSQL("CREATE TABLE season ( " +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT,  " +
					"SeasonName TEXT);");

			DBInstance.execSQL("CREATE TABLE ext_type ( " +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " +
					"Name TEXT, " +
					"Type TEXT);");
			
			DBInstance.execSQL("CREATE TABLE ext_value ( " +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " +
					"Name TEXT DEFAULT ( '' ), " +
					"Value TEXT DEFAULT ( '' ), " +
					"TeamId INTEGER, " +
					"ExtType INTEGER, " +
					"FOREIGN KEY ( ExtType ) REFERENCES ext_type ( Id ));");
			
			DBInstance.execSQL("CREATE TABLE updates ( " +
			        "UpdatedTable TEXT PRIMARY KEY ASC ON CONFLICT FAIL, " +
					"UpdatedDate NUMERIC);");
			
			setupExtTypes(DBInstance);
			
			SQLiteDatabase.releaseMemory();
		}
		
		private void setupExtTypes(SQLiteDatabase DBInstance) {
			ContentValues cv = new ContentValues();
			
			cv.put("Name", Extension.CastType.INT.toString());
			cv.put("Type", Extension.CastType.INT.toInt());
			DBInstance.insert("ext_type", null, cv);
			cv.clear();
			
			cv.put("Name", Extension.CastType.STR.toString());
			cv.put("Type", Extension.CastType.STR.toInt());
			DBInstance.insert("ext_type", null, cv);
			cv.clear();
			
			cv.put("Name", Extension.CastType.BOOL.toString());
			cv.put("Type", Extension.CastType.BOOL.toInt());
			DBInstance.insert("ext_type", null, cv);
			cv.clear();
			
			cv.put("Name", Extension.CastType.LONG.toString());
			cv.put("Type", Extension.CastType.LONG.toInt());
			DBInstance.insert("ext_type", null, cv);
			cv.clear();
		}
		
		@Override
		public void onOpen(SQLiteDatabase DBInstance) {
			DBInstance.setVersion(DATABASE_VERSION);
			
			//DBInstance.needUpgrade(DATABASE_VERSION);
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase DBInstance, int oldVersion, int newVersion) {
			//for now just drop and recreate!
			//TODO: A proper DB upgrade, involving from which version the upgrade is being 
			// performed from and which database version it's being upgraded too.
			switch (oldVersion) {
			case 1:
			default:
				DBInstance.execSQL("DROP TABLE IF EXISTS division");
				DBInstance.execSQL("DROP TABLE IF EXISTS team");
				DBInstance.execSQL("DROP TABLE IF EXISTS season");
				DBInstance.execSQL("DROP TABLE IF EXISTS ext_type");
				DBInstance.execSQL("DROP TABLE IF EXISTS game");
				DBInstance.execSQL("DROP TABLE IF EXISTS ext_value");
				DBInstance.execSQL("DROP TABLE IF EXISTS updates");
				
				onCreate(DBInstance);
				
				break;
			}
			
			SQLiteDatabase.releaseMemory();
		}
	}
	
	//---method to update the database based on the object data passed in
	public boolean updateObject(Table tbl, Object genericObj) {
		ContentValues updObj = new ContentValues();
		boolean rv = false;
		this.open();
		
		try {
			switch(tbl) {
			case SEASON:
				updObj.put("SeasonName", ((Season)genericObj).name);
				
				rv = (DBInstance.update(tbl.Name(), updObj, "SeasonId=?", new String[] { Integer.toString(((Season)genericObj)._id) } ) != 0); 
				break;
			case TEAM:
				updObj.put("TeamName", ((Team)genericObj).name);
				updObj.put("Acro", ((Team)genericObj).acro);
				updObj.put("DivisionId", ((Team)genericObj).divisionId);
				updObj.put("GamesPlayed", ((Team)genericObj).gamesPlayed);
				updObj.put("Wins", ((Team)genericObj).wins);
				updObj.put("Losses", ((Team)genericObj).losses);
				updObj.put("Points", ((Team)genericObj).points);
				
				rv = (DBInstance.update(tbl.Name(), updObj, "Id=?", new String[] { Integer.toString(((Team)genericObj)._id) } ) != 0);
				
				break;
			case GAME:
				updObj.put("GameDate", ((Game)genericObj).gameDate.getTime());
				updObj.put("HomeTeamId", ((Game)genericObj).homeTeamId);
				updObj.put("AwayTeamId", ((Game)genericObj).awayTeamId);
				updObj.put("HomeScore", ((Game)genericObj).homeScore);
				updObj.put("AwayScore", ((Game)genericObj).awayScore);
				updObj.put("SeasonId", ((Game)genericObj).seasonId);
				
				rv = (DBInstance.update(tbl.Name(), updObj, "Id=?", new String[] { Integer.toString(((Game)genericObj)._id) } ) != 0);
				break;
			case DIVISION:
				updObj.put("DivisionName", ((Division)genericObj).name);
				updObj.put("DivisionAcro", ((Division)genericObj).acro);
				
				rv = (DBInstance.update(tbl.Name(), updObj, "Id=?", new String[] { Integer.toString(((Division)genericObj)._id) } ) != 0);
				break;
			case EXTENSION:
				updObj.put("Name", ((Extension)genericObj).name);
				updObj.put("Value", ((Extension)genericObj).value);
				updObj.put("TeamId", ((Extension)genericObj).teamId);
				updObj.put("ExtType", ((Extension)genericObj).extType());
				
				rv = (DBInstance.update("EXT_VALUE", updObj, "Id=?", new String[] { Integer.toString(((Extension)genericObj)._id) } ) != 0);
				break;
			default:
				break;
			}
			
			if (rv) {
				ContentValues saveObj = new ContentValues();
				
				DBInstance.delete("Updates", "UpdatedTable=?", new String[] { tbl.Name() });
				saveObj.put("UpdatedTable", tbl.Name());
				saveObj.put("UpdatedDate", (new Date()).getTime());
				DBInstance.insert("Updates", null, saveObj);
			}
			
			return rv;
		} 
		catch (SQLiteException sqlE) {
			Logger.LogError(TAG, sqlE);
		}
		catch (Exception e) {
			Logger.LogError(TAG, e);
		}
		
		this.close();
		return rv;
	}

	public boolean saveObject(Table tbl, Object genericObj) {
		ContentValues saveObj = new ContentValues();
		int tempId = -1;
		boolean rv = false;
		this.open();
		
		try {
			switch(tbl) {
			case SEASON:
				saveObj.put("SeasonName", ((Season)genericObj).name);
				tempId = (int) DBInstance.insert(tbl.tblName, null, saveObj);
				if (tempId != -1) { 
					((Season)genericObj)._id = tempId;
					rv = true;
				}
				break;
			case TEAM:
				saveObj.put("TeamName", ((Team)genericObj).name);
				saveObj.put("Acro", ((Team)genericObj).acro);
				saveObj.put("DivisionId", ((Team)genericObj).divisionId);
				saveObj.put("GamesPlayed", ((Team)genericObj).gamesPlayed);
				saveObj.put("Wins", ((Team)genericObj).wins);
				saveObj.put("Losses", ((Team)genericObj).losses);
				saveObj.put("Points", ((Team)genericObj).points);
				tempId = (int) DBInstance.insert(tbl.Name(), null, saveObj);
				if (tempId != -1) {
					((Team)genericObj)._id = tempId;
					rv = true;
				}
				break;
			case GAME:
				saveObj.put("GameDate", ((Game)genericObj).gameDate.getTime());
				saveObj.put("HomeTeamId", ((Game)genericObj).homeTeamId);
				saveObj.put("AwayTeamId", ((Game)genericObj).awayTeamId);
				saveObj.put("HomeScore", ((Game)genericObj).homeScore);
				saveObj.put("AwayScore", ((Game)genericObj).awayScore);
				saveObj.put("SeasonId", ((Game)genericObj).seasonId);
				tempId = (int) DBInstance.insert(tbl.Name(), null, saveObj);
				if (tempId != -1) {
					((Game)genericObj)._id = tempId;
					rv = true;
				}
				break;
			case EXTENSION:
				saveObj.put("Name", ((Extension)genericObj).name);
				saveObj.put("Value", ((Extension)genericObj).value);
				saveObj.put("TeamId", ((Extension)genericObj).teamId);
				saveObj.put("ExtType", ((Extension)genericObj).extType());
				
				tempId = (int) DBInstance.insert("ext_value", null, saveObj);
				if (tempId != -1) {
					((Extension)genericObj)._id = tempId;
					rv = true;
				}
				break;
			case DIVISION:
				saveObj.put("DivisionName", ((Division)genericObj).name);
				saveObj.put("DivisionAcro", ((Division)genericObj).acro);
				
				tempId = (int) DBInstance.insert(tbl.Name(), null, saveObj);
				if (tempId != -1) {
					((Division)genericObj)._id = tempId;
					rv = true;
				}
			default:
				break;
			}
			
			this.close();
			return rv;
		} 
		catch (SQLiteException sqlE) {
			Logger.LogError(TAG, sqlE);
			return false;
		}
		catch (Exception e) {
			Logger.LogError(TAG, e);
			return false;
		}
	}
	
	public Object loadObject(Table tbl, String name) {
		Object genericObj = null;
		Cursor cursor = null;
		String colName;
		
		try {
			switch (tbl.Index()) {
				case 0:
					colName = "SeasonName"; 
					break;
				case 1:
					colName = "TeamName";
					break;
				case 3:
					colName = "DivisionName";
					break;
				default:
					throw new IllegalArgumentException();	
			}
			
			this.open();
			cursor = DBInstance.rawQuery("SELECT id FROM " + tbl.tblName + " WHERE " + colName + " = ?", new String[] { name });
			if (isValidCursor(cursor)) {
				genericObj = loadObject(tbl, cursor.getInt(0), 0);
			}
		} catch (SQLiteException sql3E) {
			Logger.LogError(sql3E);
		} catch (SQLException sqlE) {
			Logger.LogError(sqlE);
		} catch (Exception e) {
			Logger.LogError(e);
		} finally {
			if (DBInstance.isOpen())
				this.close();
			
			SQLiteDatabase.releaseMemory();
		}
		
		return genericObj;
	}
	
	/***
	 * 
	 * @param tbl - {@link Table} object
	 * @param primaryIdx - passing a non-zero primary key will result in the Object returned being defined by the {@link Table} object
	 * @param secondaryIdx - passing the secondary key will always result in the return Object being a HashMap of the objects defined by the {@link Table}
	 * @return An object either defined by the {@link Table} object passed in or a HashMap of objects defined by the {@link Table} passed in.
	 */
	public Object loadObject(Table tbl, int primaryIdx, int secondaryIdx) {
		Object genericObj = null;
		Cursor cursor = null;
		Map<Long, Object> map = null;
		boolean isHashMap = false;
		
		try {
			this.open();
			genericObj = tbl.objContainer();
			
			if (primaryIdx == 0 && secondaryIdx == 0) {
				//returning all the items from the table
				isHashMap = true;
				cursor = DBInstance.rawQuery(tbl.SelectQuery(true, false), null);
			} else {
				if (primaryIdx != 0) {
					//we are only retrieving a single instance based on the Primary key index
					cursor = DBInstance.rawQuery(tbl.SelectQuery(false, true), new String[] { Integer.toString(primaryIdx) });
				} else {
					isHashMap = true;
					//we maybe retrieving multiple instances based on the secondary index
					cursor = DBInstance.rawQuery(tbl.SelectQuery(false, false), new String[] { Integer.toString(secondaryIdx) });
				}
			}
			
			if (isValidCursor(cursor)) {
				switch(tbl) {
				case SEASON:
					genericObj = new Season();
					if (isHashMap) {
						map = new HashMap<Long, Object>();
						do {
							genericObj = loadSeason(cursor);
							map.put((long) cursor.getInt(0), (Season)genericObj);
						} while (cursor.moveToNext());
						return map;
					} else {
						return loadSeason(cursor);
					}
				case TEAM:
					genericObj = new Team();
					if (isHashMap) {
						map = new HashMap<Long, Object>();
						do {
							genericObj = loadTeam(cursor);
							map.put((long) cursor.getInt(0), (Team)genericObj);
						} while (cursor.moveToNext());
						return map;
					} else {
						return loadTeam(cursor);
					}
				case GAME:
					genericObj = new Game();
					if (isHashMap)  {
						map = new HashMap<Long, Object>();
						do {
							genericObj = loadGame(cursor);
							map.put((long) cursor.getInt(0), (Game)genericObj);
						} while (cursor.moveToNext());
						return map;
					} else {
						return loadGame(cursor);
					}
				case DIVISION:
					genericObj = new Division();
					if (isHashMap) {
						map = new HashMap<Long, Object>();
						do {
							genericObj = loadDivision(cursor);
							map.put((long) cursor.getInt(0), (Division)genericObj);
						} while (cursor.moveToNext());
						return map;
					} else {
						return loadDivision(cursor);
					}
				case EXTENSION:
					genericObj = new Extension();
					if (isHashMap) {
						HashMap<String, Object> extMap = new HashMap<String, Object>();
						do {
							genericObj = loadExtension(cursor);
							extMap.put(cursor.getString(1), (Extension)genericObj);
						} while (cursor.moveToNext());
						return extMap;
					} else {
						return loadExtension(cursor);
					}
				default:
					break;
				}
			} else {
				genericObj = null;
			}
		} catch (SQLiteException sql3E) {
			Logger.LogError(sql3E);
		} catch (SQLException sqlE) {
			Logger.LogError(sqlE);
		} catch (Exception e) {
			Logger.LogError(e);
		} finally {
			this.close();
			SQLiteDatabase.releaseMemory();
		}
		
		return genericObj;
	}
	
	/***
	 * Private method used to load the current record in the cursor into a 
	 * new {@link Team} object.
	 * @param cursor containing a row item of season.
	 * @return {@link Team} object
	 */
	private Team loadTeam(Cursor cursor) {
		Team team = null;
		
		team = new Team();
		team._id = cursor.getInt(0);
		team.name = cursor.getString(1);
		team.acro = cursor.getString(2);
		team.divisionId = cursor.getInt(3);
		team.gamesPlayed = cursor.getInt(4);
		team.wins = cursor.getInt(5);
		team.losses = cursor.getInt(6);
		team.points = cursor.getInt(7);
		
		return team;
	}
	
	/***
	 * Private method used to load the current record in the cursor into a 
	 * new {@link Season} object.
	 * @param cursor containing a row item of season.
	 * @return {@link Season} object
	 */
	private Season loadSeason(Cursor cursor) {
		Season season = null;

		season = new Season();
		season._id = cursor.getInt(0);
		season.name = cursor.getString(1);

		return season;
	}

	/***
	 * Private method used to load the current record in the cursor into a 
	 * new {@link Division} object.
	 * @param cursor containing a row item of season.
	 * @return {@link Division} object
	 */
	private Division loadDivision(Cursor cursor) {
		Division division = null;
		
		division = new Division();
		division._id = cursor.getInt(0);
		division.name = cursor.getString(1);
		division.acro = cursor.getString(2);

		return division;
	}

	/***
	 * Private method used to load the current record in the cursor into a 
	 * new {@link Extension} object.
	 * @param cursor containing a row item of season.
	 * @return {@link Extension} object
	 */
	private Extension loadExtension(Cursor cursor) {
		Extension extension = null;
		
		extension = new Extension();
		extension._id = cursor.getInt(0);
		extension.name = cursor.getString(1);
		extension.value = cursor.getString(2);
		extension.teamId = cursor.getInt(3);
		extension.setExtType(cursor.getInt(4));
		
		return extension;
	}

	/***
	 * Private method used to load the current record in the cursor into a 
	 * new {@link Game} object.
	 * @param cursor containing a row item of season.
	 * @return {@link Game} object
	 */
	private Game loadGame(Cursor cursor) {
		Game game = null;

		game = new Game();
		game._id = cursor.getInt(0);
		game.gameDate = new Date(cursor.getLong(1));
		game.homeTeamId = cursor.getInt(2);
		game.awayTeamId = cursor.getInt(3);
		game.homeScore = cursor.getInt(4);
		game.awayScore = cursor.getInt(5);
	
		return game;
	}
}